﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.VisualBasic;
using System.Data;
using System.Globalization;

namespace InterestCalculationBiz
{
  public static class LoanMethod
    {
        /// <summary>
        /// function to create next interest date
        /// </summary>
        /// <param name="date">apply date as string/datetime format(mm/dd/yyyy)</param>
        /// <returns></returns>
        public static string NextInterestDate(string date, string payhash, double balance)
        {
            string _tempdate = "";
            try
            {
                //=IF(L18<0.01,0,EOMONTH(H18,IF(OR(AND(G18="Advance",H18=EOMONTH(H18,0)),G18="Interest"),1,0)))
                if (balance < 0.01)
                {
                    _tempdate = "";
                }
                else
                {
                    if ((payhash == "advance" && Convert.ToDateTime(date) == Convert.ToDateTime(getEOM(date))) || (payhash == "interest"))
                    {
                        date = CDate(Convert.ToDateTime(date).AddMonths(1));
                    }
                    _tempdate = CDate(Convert.ToDateTime(getEOM(date)));
                }
            }
            catch (Exception)
            {
                _tempdate = "";
            }
            return _tempdate;
        }
        /// <summary>
        /// funtion for calculate next repayment payment date.It only work for greater then one row.
        /// </summary>
        /// <param name="freq">this may be weekly=0,fortnighty=1,monthly=2</param>
        /// <param name="date">loan date</param>
        /// <param name="f_repaymentDate">User First Repayment Date</param>
        /// <param name="pre_nextrepaymentate">Previous row nextRepayment Date</param>
        /// <param name="pre_nextinterestdate">Previous row next interest date</param>
        /// <param name="intersdate">This row interes date</param>
        /// <param name="balance">Balance </param>
        /// <returns>Next repayment date</returns>
        public static string NextReapymentDate(int freq, string date, string f_repaymentDate, string prev_repaymentdate, string prev_interstdate, string intersdate, double balance)
        {
            //=IF(L19>0.001,IF($D$6=30,MAX($I$6,EOMONTH(H19,0)+DAY($I$6)),E18+IF(D18=D19,$D$6,0)),0)
            string _tempdate = "";
            if (balance > 0.01)
            {   //EOMONTH(H19,0)
                DateTime _lastDate = Convert.ToDateTime(getEOM(date));
                DateTime _fRepaymentDate = Convert.ToDateTime(f_repaymentDate);
                //EOMONTH(H19,0)+DAY($I$6)
                DateTime _tempLoanDate = _lastDate.AddDays(_fRepaymentDate.Day);
                if (NoofDays(freq) == 30)
                {
                    if (Convert.ToDateTime(_fRepaymentDate) > _tempLoanDate)
                    {
                        _tempdate = CDate(_fRepaymentDate);
                    }
                    else
                    {
                        _tempdate = CDate(_tempLoanDate);
                    }
                }
                else
                { //E18+IF(D18=D19,$D$6,0)
                    int _adddays = 0;
                    if (Convert.ToDateTime(intersdate) == Convert.ToDateTime(prev_interstdate))
                    {
                        _adddays = NoofDays(freq);
                    }
                    else
                    {
                        _adddays = 0;
                    }
                    _tempdate = CDate(Convert.ToDateTime(prev_repaymentdate).AddDays(_adddays));
                }
            }
            else
            {
                _tempdate = "";
            }
            return _tempdate;
        }
        /// <summary>
        /// Return no of days 
        /// </summary>
        /// <param name="freq">this may be weekly=0,fortnighty=1,monthly=2</param>
        /// <returns></returns>
        public static int NoofDays(int freq)
        {
            int _noOfDays = 0;
            if (freq == 0)
            {
                _noOfDays = 7;
            }
            else if (freq == 1)
            {
                _noOfDays = 14;
            }
            else
            {
                _noOfDays = 30;
            }
            return _noOfDays;
        }
       
        /// <summary>
        /// function for calculate loandate
        /// </summary>
        /// <param name="pre_nextinterestdate">Previous row next interest date</param>
        /// <param name="pre_nextrepaymentate">Previous row nextRepayment Date</param>
        /// <returns></returns>
        public static string LoanDate(string Prev_InterestDate, string Prev_RepaymentDate)
        {
            string _tempDate = "";
            if (Convert.ToDateTime(Prev_InterestDate) < Convert.ToDateTime(Prev_RepaymentDate))
            {
                _tempDate = Prev_InterestDate;
            }
            else
            {
                _tempDate = Prev_RepaymentDate;
            }
            return _tempDate;
        }
        /// <summary>
        /// Calculate pay # value
        /// </summary>
        /// <param name="date">Loan Date</param>
        /// <param name="pre_nextinterestdate">Previous row next interest date</param>
        /// <param name="pre_nextrepaymentate">Previous row nextRepayment Date</param>
        /// <param name="f_repaymentDate">User First Repayment Date</param>
        /// <param name="maxPayHash">Previous Maximum number in column pay #</param>
        /// <returns></returns>
        public static string PayHash(string date, string pre_nextinterestdate, string pre_nextrepaymentate, string f_repaymentDate, int maxPayHash)
        {
            string _payHash = "";
            if (Convert.ToDateTime(date) == Convert.ToDateTime(pre_nextinterestdate) && Convert.ToDateTime(date) != Convert.ToDateTime(pre_nextrepaymentate))
            {
                _payHash = "interest";
            }
            else
            {
                if (Convert.ToDateTime(date) < Convert.ToDateTime(f_repaymentDate) || date == "")
                {
                    _payHash = "";
                }
                else
                {
                    _payHash = Convert.ToString(maxPayHash + 1);
                }
            }
            return _payHash;
        }
      /// <summary>
      /// 
      /// </summary>
      /// <param name="payhash"></param>
      /// <param name="date"></param>
      /// <param name="pre_nextRepaymentDate"></param>
      /// <param name="repayment"></param>
      /// <param name="interest"></param>
      /// <param name="balance"></param>
      /// <returns></returns>
        public static double Payment(string payhash,string date,string pre_nextRepaymentDate,double repayment,double interest,double pre_balance)
        {
            //=IF(G19>0,IF(H19=E18,MIN($I$9,J19+L18),0),0)
            double _payment = 0;
            if (payhash != "")
            {
                if (IsNumeric(payhash))
                {
                    if (Convert.ToInt32(payhash)>0)
                    {
                        if (Convert.ToDateTime(date) == Convert.ToDateTime(pre_nextRepaymentDate))
                        {
                            if (repayment > (interest + pre_balance))
                            {
                                _payment = interest + pre_balance;
                            }
                            else {
                                _payment = repayment; 
                            }
                        }
                    }
                }
            }
            return _payment;
        }
      /// <summary>
      /// Check weather object is numeric or not
      /// </summary>
      /// <param name="_obj"></param>
      /// <returns>Bolean value true/false</returns>
        public static Boolean IsNumeric(object _obj)
        {
            double _temp = 0;
            try
            {
                _temp = Convert.ToDouble(_obj);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
      /// <summary>
      /// Return value if supplied value is numeric else 0
      /// </summary>
      /// <param name="_obj"></param>
      /// <returns></returns>
        public static double ReturnIfNumeric(object _obj)
        {
            double _temp = 0;
            try
            {
                _temp = Convert.ToDouble(_obj);
            }
            catch (Exception)
            {
                _temp = 0;
            }
            return _temp;
        }
       /// <summary>
       /// Calculate Interest  
       /// </summary>
       /// <param name="date">Current row date</param>
       /// <param name="pre_date">Previous row date </param>
       /// <param name="daysinyear">Days in year</param>
       /// <param name="extraday">Extra days calculated by system</param>
       /// <param name="balance">Prevoius row balance</param>
       /// <param name="rate">Rate of interest</param>
       /// <returns>Calculate Interest</returns>
        public static double Interest(string date,string pre_date,int daysinyear,int extraday,double pre_balance,double rate)
        {
            //=IF(H19=0,0,ROUND((H19-H18+C19)/$D$4*$I$4*L18,2))
            double _interest = 0;
            if (date != "" && pre_date!="")
            {
                  DateTime _date = Convert.ToDateTime(date);
                  DateTime _pre_date = Convert.ToDateTime(pre_date);
                 _interest  =Math.Round(((((_date - _pre_date).TotalDays + extraday)/ daysinyear*pre_balance*rate))/100,2) ;
            }
            return _interest; 
        }
        public static double Interest_before_adv(string payhash,string date,string lastpaymentdate,string pre_maxDate,double sum_of_pre_interest_before_adv,double sum_of_pre_interest)
        {
            //=IF(OR(G20="Interest",H20=$I$10),ROUND(SUMIF($H$18:H20,"<="&H20,$M$18:M20)-SUM($J$18:J19),2),0)
            double _totalinterest = 0;
            if (payhash == "interest" || Convert.ToDateTime(date) == Convert.ToDateTime(lastpaymentdate))
            {
                 double _sum_of_pre_interest=0;
                 //SUMIF($H$18:H20,"<="&H20,$M$18:M20
                 if(Convert.ToDateTime(pre_maxDate)<=Convert.ToDateTime(date))
                 {
                     _sum_of_pre_interest = sum_of_pre_interest;
                 }
                 _totalinterest = Math.Round(_sum_of_pre_interest - sum_of_pre_interest_before_adv, 2);  
            }
            return _totalinterest;
        }
      /// <summary>
      /// calculate balance
      /// </summary>
      /// <param name="pre_balance"></param>
      /// <param name="payment"></param>
      /// <param name="pre_interest"></param>
      /// <param name="advcharges"></param>
      /// <returns></returns>
        public static double Balance(double pre_balance,double payment,double interest_before_adv,double advcharges)
        {
            double _balance = 0;
            _balance = Math.Round(pre_balance - payment + interest_before_adv + advcharges,2);
            return _balance;
            //=ROUND(L18-I19+J19+K19,2)
        }
      /// <summary>
      /// Calculate disc factor
      /// </summary>
      /// <param name="payhash">Pay # value</param>
      /// <param name="trailrate">trail rate</param>
      /// <returns>Double Value</returns>
        public static double DiscFactor(string payhash, double trailrate)
        {
            double _discfactor = 0;
            if (IsNumeric(payhash))
            {
                _discfactor =Math.Round(1 / Math.Pow(1 + trailrate, Convert.ToDouble(payhash)),8);
            }
            return _discfactor;
        }
      /// <summary>
      /// Calculate Extradays
      /// </summary>
      /// <returns>integer value</returns>
        public static int ExtraDays(string payhash,string pre_payhash)
        {
            //=IF(G18="interest",1,IF(OR(G17="interest"),-1,0))
            int _extraday = 0;
            if (payhash == "interest")
            {
                _extraday = 1;
            }
            else {
                if (pre_payhash == "interest")
                {
                    _extraday = -1;
                }
                else {
                    _extraday = 0;
                }
            }
            return _extraday;

        }
      /// <summary>
      ///  Calculate last repayment date
      /// </summary>
      /// <param name="loanduaration">loan duration in weeks</param>
      /// <param name="freq">fequency 0=weekly,1=fortnighty,2=monthly</param>
      /// <param name="f_repaymentdate">repayment date</param>
      /// <returns>return date as string</returns>
        public static string LastRepaymentDate(double loanduaration, int freq, string f_repaymentdate, int NoofInstallment = 0)
        {    //=IF(D6=30,EOMONTH(I6,I11-2)+DAY(I6),I6+(I11-1)*D6)
            string _lastRepaymentDate = "";
            int noofdays = NoofDays(freq);
            int noofinstallment = No_of_Installments(loanduaration, freq,NoofInstallment);
            DateTime _f_repaymentdate=Convert.ToDateTime(f_repaymentdate);
            if (noofdays == 30)
            {
                _lastRepaymentDate = CDate(Convert.ToDateTime(getEOM(_f_repaymentdate.AddMonths(noofinstallment - 2).ToString())).AddDays(_f_repaymentdate.Day)); 
            }
            else {
                _lastRepaymentDate = CDate(_f_repaymentdate.AddDays((noofinstallment - 1) * noofdays));
            }
            return _lastRepaymentDate;
        }
      /// <summary>
      /// Calculate no of installment
      /// </summary>
      /// <param name="loanduaration">loan duration in weeks</param>
      /// <param name="freq">fequency 0=weekly,1=fortnighty,2=monthly</param>
      /// <returns>integer value</returns>
        public static int No_of_Installments(double loanduration,int freq,int NoofInstallment=0)
        {
            //=IF(I1=B1,I3,IF(I1=B2,ROUNDUP(I3/2,0),ROUNDUP(I3*7/365*12,0)))
            int _no_of_installment = 0;
            if (NoofInstallment == 0)
            {
                if (freq == 0)
                {
                    _no_of_installment = (int)Math.Round(loanduration, 0);
                }
                else if (freq == 1)
                {
                    _no_of_installment = (int)Math.Round(loanduration / 2, 0);
                }
                else
                {
                    _no_of_installment = (int)Math.Round(loanduration * 7 / 365 * 12, 0);
                }
            }
            else {
                _no_of_installment = NoofInstallment;
            }
            return _no_of_installment;
        }
        public static double Adv_Charges(string payhash,string date,string lastrepaymentdate,double MonthlyFee)
        {
            double _adv_charges = 0;
            //=IF(AND(G19="interest",H19<=$I$10),IF(L18>0.005,$N$13,0),0)
            if (payhash == "interest" && Convert.ToDateTime(date)<=Convert.ToDateTime(lastrepaymentdate))
            {
                _adv_charges = MonthlyFee;
            }
            return _adv_charges;
        }

        /* this function not used till now*/
        public static double CompundeFactor(int freq)
        {
            double _compundfactor = 0;
            if (freq == 0)
            {
                _compundfactor = 52.18;
            }
            else if (freq == 1)
            {
                _compundfactor = 26.09;
            }
            else {
                _compundfactor = 12;
            }
            return _compundfactor;
        }
        public static double trailRate(int freq,double _max_interestRate)
        {
            double _trailRate = 0;
            double _noofdays = 0;
            _noofdays = NoofDays(freq);
            _trailRate =Math.Round((_max_interestRate / 100) * (_noofdays / 365),8);
            return _trailRate;
        }
        public static double ComparisonRate(int freq,double _max_interest)
        {
            double _comparisonrate = 0;
            double _trailrate = 0;
            double _compoundfactor = 0;
        
            _trailrate = trailRate(freq,_max_interest);
            _compoundfactor = CompundeFactor(freq);
            _comparisonrate = _trailrate * _compoundfactor;
            return _comparisonrate;
        }
        //public static double RevisedRepayment(int freq, double loanduration, double principal, double _max_interest)
        //{
        //    double _revisedRepayment = 0;
        //    double _comparisonRate = ComparisonRate(freq, _max_interest);
        //    double _compoundFactore = CompundeFactor(freq);
        //    double _noofinstallment = No_of_Installments(loanduration, freq);
        //    _revisedRepayment = Math.Round(Financial.Pmt(_comparisonRate / _compoundFactore, _noofinstallment, -principal), 2) + 0.02;
        //    return _revisedRepayment;
        //}
        /// <summary>
        /// Calculate repayment 
        /// </summary>
        /// <param name="desiredACR">Calculated desired acr</param>
        /// <param name="loanduaration">loan duration in weeks</param>
        /// <param name="freq">fequency 0=weekly,1=fortnighty,2=monthly</param>
        /// <param name="principal">principal amount</param>
        /// <returns></returns>
        public static double RevisedRepayment(double presentACR,int freq, double loanduration, double principal,int NoofInstallment=0)
        {
            //=ROUNDUP(PMT(N10/D5,I11,-I2),2)+0.02
            double _repayment = 0;
           // double _comparisonRate = ComparisonRate(freq);
            double _compoundFactore = CompundeFactor(freq);
            double _noofinstallment = No_of_Installments(loanduration, freq,NoofInstallment);
            _repayment = ROUNDUP(Financial.Pmt((presentACR / 100) / _compoundFactore, _noofinstallment, -principal),2) + 0.02;
            return _repayment;
        }
        public static double Repayment(double desiredACR, int freq, double loanduration, double principal,int NoofInstallment=0,double Repayment=0)
        {
            
            double _repayment = 0;
            // double _comparisonRate = ComparisonRate(freq);
            if (Repayment == 0)
            {
                double _compoundFactore = CompundeFactor(freq);
                double _noofinstallment = NoofInstallment;
                _noofinstallment = No_of_Installments(loanduration, freq, NoofInstallment);
                _repayment = ROUNDUP(Financial.Pmt(desiredACR / _compoundFactore, _noofinstallment, -principal), 2);
            }
            else {
                _repayment = Repayment;
            }
            return _repayment;
        }

      /// <summary>
      /// Calculate initial installment
      /// </summary>
      /// <param name="Max_Interest">Maximum interest rate</param>
      /// <param name="Frequency">Loan frequency</param>
      /// <param name="LoanDuration">Loan Duration</param>
      /// <param name="advCharges">Advance Charges or Total Funded Amount</param>
      /// <param name="Pre_First_Repayment_Interest">First Repayment interest calculated in loan table</param>
      /// <param name="MonthlyFee">Monthly fee</param>
      /// <returns>Initial Installment as a double</returns>
        public static double InitialInstallment(double Max_Interest, int Frequency, double LoanDuration, double advCharges, double Pre_First_Repayment_Interest, double MonthlyFee, int NoofInstallment = 0)
        {
            //=ROUND(PMT(I4/D5,I11,-Q17-I7,,1),2)+N13*3
            double _initialInstallment = 0;
            double _compoundFator=CompundeFactor(Frequency); 
            double _noofInstallment=No_of_Installments(LoanDuration,Frequency,NoofInstallment);
            _initialInstallment = Math.Round(Financial.Pmt((Max_Interest/100) / _compoundFator, _noofInstallment, (-advCharges - Pre_First_Repayment_Interest),0,DueDate.BegOfPeriod), 2) + (MonthlyFee * 3);
            return _initialInstallment;
        }
        public static double SimpleInterest(string LoanDate,string F_RepaymentDate,double InterestRate,double advCharges)
        {
            double _simpleInterest = 0;
            try
            {
                _simpleInterest = Math.Max((Convert.ToDateTime(F_RepaymentDate).Day - Convert.ToDouble(LoanDate) - 45) / 365, 0);
            }
            catch (Exception)
            {
                _simpleInterest = 0;
            }
            return _simpleInterest;
        }
        //public static void do_all(int interest,int freq)
        //{
        //   double _interest=37.52;
        //    //Range("q24") = Round(Range("i4") * Range("d6") / 365, 8)
        //   double _trailRate = trailRate(freq, _interest);
        //   double _intialInstallment=
          
        //}
      /// <summary>
      /// Calculate EMI 
      /// </summary>
      /// <param name="InitialInstallment"></param>
      /// <param name="Balance"></param>
      /// <param name="Interest"></param>
      /// <param name="advCharges"></param>
      /// <param name="TrailRate"></param>
      /// <param name="propObj"></param>
      /// <returns></returns>
        public static double Cal_EMI(double InitialInstallment, double balance, double Interest, double advCharges, double TrailRate, LoanProperties propObj, int NoofInstallment = 0, Boolean isAdvance = true, string advancevalue = "", Boolean IsSinglePayment = false, double Pre_Repayment = 0, int ApplyPayhash = 0, int lastchangespayhash = 0, double Previous_Repayment = 0,Boolean NoChange=false)
        {
            double _repayment = 0;
            double _x = 0;
            double _lastInstallment = InitialInstallment;
            _repayment = InitialInstallment;
            _x = _repayment / 10;
            DataTable _dtLoan;
            DataTable _dtDifference;
            while (balance > 0)
            {
                _repayment =Math.Round(_repayment + _x,2);
                _dtLoan = CalculateLoanTable(Interest, _repayment, propObj, advCharges, TrailRate, NoofInstallment, isAdvance, advancevalue, IsSinglePayment, Pre_Repayment, ApplyPayhash, lastchangespayhash,Previous_Repayment,NoChange);
                _dtDifference = Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, Interest,"",NoofInstallment).Tables["DifferenceTable"];
                balance = Balance(_dtDifference); 
            }
            while (_x >= 0.01)
            {
                while (balance < 0.005)
                {
                    _lastInstallment = _repayment;
                    _repayment =Math.Round(_lastInstallment - _x,2);
                    _dtLoan = CalculateLoanTable(Interest, _repayment, propObj, advCharges, TrailRate, NoofInstallment, isAdvance, advancevalue, IsSinglePayment, Pre_Repayment, ApplyPayhash, lastchangespayhash,Previous_Repayment,NoChange);
                    _dtDifference = Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, Interest,"",NoofInstallment).Tables["DifferenceTable"];
                    balance = Balance(_dtDifference); 
                }
                _repayment = Math.Round(_lastInstallment,2);
                _dtLoan = CalculateLoanTable(Interest, _repayment, propObj, advCharges, TrailRate, NoofInstallment, isAdvance, advancevalue, IsSinglePayment, Pre_Repayment, ApplyPayhash, lastchangespayhash,Previous_Repayment,NoChange);
                 _dtDifference = Cal_Difference_dataTable(_dtLoan, propObj, advCharges, _repayment, Interest,"",NoofInstallment).Tables["DifferenceTable"];
                balance = Balance(_dtDifference); 
                _x =_x / 3;
            }
            return _repayment; 
        }
      /// <summary>
      /// get value of balance column from difference table
      /// </summary>
      /// <param name="dtDifference">pass difference datatable which consist of balance column</param>
      /// <returns></returns>
        public static double Balance(DataTable dtDifference)
        {
            double _balance = 0;
            try { _balance = Convert.ToDouble(dtDifference.Rows[0]["Balance"]); }
            catch (Exception) { }
            return _balance;
        }
      /// <summary>
        /// Calculate Maximum interest,comparison rate,trail rate and return as datatable with column name "Interest","ComparisonRate" and "TrailRate"
      /// </summary>
      /// <param name="DesiredACR">Pass desired acr for e.g 0.48</param>
      /// <param name="propObj">pass loan properties that have all value input by the user. See property class.</param>
        /// <param name="advCharges">Advance charges will be sum of Principal,BrokerageFee,CreditSearchFee,MonthlyFee,SecurityFee</param>
        /// <returns>As a datatabe with one row and three column name "Interest","ComparisonRate" and "TrailRate"</returns>
        public static double Max_InterestRate(double DesiredACR,LoanProperties propObj,double advCharges)
        {  
            double _req_acr = DesiredACR;
            double _repayment = Repayment(_req_acr,propObj.Frequency, propObj.LoanDuration, propObj.Principal);
            double _interest = _req_acr / 2;
            double _compoundfactor = CompundeFactor(propObj.Frequency);
            int i = 1;
             double _trailrate = 0;
            _trailrate = ACR(propObj.Frequency, _interest, _repayment, propObj, advCharges);
           // _diff = Cal_Difference(CalculateLoanTable(_interest, _repayment, propObj, advCharges, _trailrate), propObj, advCharges);
            double _comparisonRate = 0;
            double _diff_acr = 0;
            _comparisonRate = _trailrate * _compoundfactor;
            _diff_acr = _req_acr - _comparisonRate;
            while (Math.Abs(_diff_acr) > 0.00005 || _req_acr < _comparisonRate)
            {
                _diff_acr = _req_acr - _comparisonRate;
                _interest = _interest + _diff_acr;
                _trailrate = ACR(propObj.Frequency, _interest,  _repayment, propObj, advCharges);
                _comparisonRate = _trailrate * _compoundfactor;
               // _diff_acr = _req_acr - _comparisonRate;
                i = i + 1;
                if (i > 50)
                {
                    return _interest;
                }
            }
            return _interest;
        }
        public static DataTable Interest_Rate(double Interest,double ComparisonRate,double TrailRate,LoanProperties propObj)
        {
            DataTable _dtAllValue = new DataTable();
            _dtAllValue.Columns.Add("Interest");
            _dtAllValue.Columns.Add("ComparisonRate");
            _dtAllValue.Columns.Add("TrailRate");
            _dtAllValue.Columns.Add("RevisedRepayment");
            DataRow dr = _dtAllValue.NewRow();
            dr[0] = Interest;
            dr[1] = ComparisonRate;
            dr[2] = TrailRate;
            dr[3] = RevisedRepayment(ComparisonRate*100, propObj.Frequency, propObj.LoanDuration, propObj.Principal);
            _dtAllValue.Rows.Add(dr);
            return _dtAllValue;
        }
        public static double ACR(int Freq,double Interest,double Repayment,LoanProperties propObj,double advCharges,int NoofInstallment=0,Boolean isAdvance=true,string advancevalue="")
        {
            double _compoundFactor=CompundeFactor(Freq);
            double _trailrate = (Interest) * NoofDays(propObj.Frequency) / 365 / 2;
            double x = 0.01;
            double intt = 0;
            DataTable dt=CalculateLoanTable(Interest*100, Repayment, propObj, advCharges, _trailrate*100,NoofInstallment,isAdvance,advancevalue);
            double Difference = Cal_Difference(dt, propObj, advCharges,NoofInstallment);
            for (int i = 1; i <= 100; i++)
            {
                if (Difference < 0.0001)
                {
                    break;
                }
                intt = _trailrate + x;
                _trailrate = intt;
               // diff = Range("q21")
                dt=CalculateLoanTable(Interest*100, Repayment, propObj, advCharges, _trailrate*100,NoofInstallment,isAdvance,advancevalue);
                Difference = Cal_Difference(dt,propObj,advCharges,NoofInstallment);     
                if (Difference < 0)
                {
                    _trailrate = intt - x;
                    x = x / 4;
                    dt = CalculateLoanTable(Interest * 100, Repayment, propObj, advCharges, _trailrate * 100,NoofInstallment,isAdvance,advancevalue);
                    Difference = Cal_Difference(dt, propObj, advCharges,NoofInstallment);     
                }
            }
            return _trailrate;
        }
       public static double ROUNDUP(double number, int digits)
        {
            return Math.Ceiling(number * Math.Pow(10, digits)) / Math.Pow(10, digits);
        }
       public static DataTable CalculateLoanTable(double interest, double RePayment, LoanProperties propObj, double advCharges, double trailRate, int NoofInstallment = 0, Boolean IsAdvance = true, string advancevalue = "", Boolean IsSinglePayment = false, double Pre_Repayment = 0, int ApplyPayhash = 0,int lastchangespayhash=0,double Previous_Repayment=0,Boolean NoChange=false)
        {
            string _lastRepaymentDate = LoanMethod.LastRepaymentDate(propObj.LoanDuration, propObj.Frequency, propObj.FirstRepaymentDate,NoofInstallment);
            DataTable dt = new DataTable();
            dt.Columns.Add("ExtraDay", typeof(Int64));
            dt.Columns.Add("nextinterest", typeof(DateTime));
            dt.Columns.Add("repaymentdate", typeof(DateTime));
            dt.Columns.Add("Payhash");
            dt.Columns.Add("date", typeof(DateTime));
            dt.Columns.Add("payment", typeof(Double));
            dt.Columns.Add("Interest_before_adv", typeof(Double));
            dt.Columns.Add("advCharges", typeof(Double));
            dt.Columns.Add("balance", typeof(Double));
            dt.Columns.Add("Interest", typeof(Double));
            dt.Columns.Add("discFator", typeof(Double));
            double balance = 0.0;
            double payment = 0;
            //Please dont change order
            balance = LoanMethod.Balance(0, payment, 0, advCharges);
            string _nextIntersdate ="";
            if (IsAdvance)
            {
                _nextIntersdate = LoanMethod.NextInterestDate(propObj.LoanDate, "advance", balance);
            }
            else {
                _nextIntersdate = LoanMethod.NextInterestDate(propObj.LoanDate,advancevalue , balance);
            }
            //=-I18+K18
            //if (balance > 0.01)
            //{ 
            //    nextInterestdate= 
            //}
            DataRow r;
            r = dt.NewRow();
            if (IsAdvance)
            {
                r[0] = LoanMethod.ExtraDays("Advance", "");
            }
            else
            {
                r[0] = LoanMethod.ExtraDays(advancevalue, "");
            }
            r[1] = _nextIntersdate;
            r[2] = propObj.FirstRepaymentDate;
            if (IsAdvance)
            {
                r[3] = "Advance";
            }
            else {
                r[3] = advancevalue;
            }
            r[4] = propObj.LoanDate;
            r[5] = 0;
            r[6] = 0;
            r[7] = advCharges;
            r[8] = advCharges - payment;
            r[9] = 0;
            r[10] = 0;
            dt.Rows.Add(r);
            int _MaxPayHash = 0;
            int rep = 0;
            while (balance > 0)
            {
                double _pre_Balance = 0;
                string _pre_nextinterestdate = dt.Rows[dt.Rows.Count - 1]["nextinterest"].ToString();
                string _pre_nextrepaymentdate = dt.Rows[dt.Rows.Count - 1]["repaymentdate"].ToString();
                if (_pre_nextrepaymentdate != "")
                {
                    string _loanDate = LoanMethod.LoanDate(_pre_nextinterestdate, _pre_nextrepaymentdate);
                    string _pre_loandate = dt.Rows[dt.Rows.Count - 1]["date"].ToString();
                    string _pre_payHash = dt.Rows[dt.Rows.Count - 1]["Payhash"].ToString();
                    int maxPahHash = 0;
                    try
                    {
                        //  dt.DefaultView.RowFilter = "[Payhash] DESC";
                        maxPahHash = _MaxPayHash;
                    }
                    catch (Exception)
                    {
                        maxPahHash = 0;
                    }
                    double s_of_pre_adv_interest = 0;
                    try
                    {
                        s_of_pre_adv_interest = Convert.ToDouble(dt.Compute("sum(Interest_before_adv)", null));
                    }
                    catch (Exception)
                    {
                        s_of_pre_adv_interest = 0;
                    }
                    double s_of_pre_interest = 0;
                    try
                    {
                        s_of_pre_interest = Convert.ToDouble(dt.Compute("sum(Interest)", null));
                    }
                    catch (Exception)
                    {
                        s_of_pre_interest = 0;
                    }
                    string _payhash = LoanMethod.PayHash(_loanDate, _pre_nextinterestdate, _pre_nextrepaymentdate, propObj.FirstRepaymentDate, maxPahHash);
                    if (LoanMethod.IsNumeric(_payhash))
                    {
                        _MaxPayHash = Convert.ToInt32(_payhash);
                    }
                    advCharges = LoanMethod.Adv_Charges(_payhash, _loanDate, _lastRepaymentDate, propObj.MonthlyFee);
                    DataColumn col = dt.Columns["date"];
                    DataTable tbl = col.Table;
                    //var maxdate = tbl.AsEnumerable()
                    //.Select(cols => cols.Field<DateTime>(col.ColumnName))
                    //.OrderByDescending(p => p.Ticks)
                    //.FirstOrDefault();
                    string maxdate = Convert.ToString(dt.Rows[dt.Rows.Count - 1]["date"]);
                    try { _pre_Balance = Convert.ToDouble(dt.Rows[dt.Rows.Count - 1]["balance"]); }
                    catch (Exception) { _pre_Balance = 0; }
                    int extraday = LoanMethod.ExtraDays(_payhash, _pre_payHash);
                    double _interest = LoanMethod.Interest(_loanDate, _pre_loandate, 365, extraday, _pre_Balance, interest);
                    double _interest_before_advance = LoanMethod.Interest_before_adv(_payhash, _loanDate, _lastRepaymentDate, Convert.ToString(maxdate), (s_of_pre_adv_interest), s_of_pre_interest);
                    double _payment = 0;
                    if ((_payhash == "interest") || (Convert.ToDateTime(_loanDate) == Convert.ToDateTime(_lastRepaymentDate)))
                    {
                        _interest_before_advance = _interest_before_advance + _interest;
                    }
                  
                    if (IsSinglePayment && _MaxPayHash==propObj.applypayhash[rep] &&(lastchangespayhash==0 || propObj.applypayhash[rep]<=lastchangespayhash) && !(NoChange))
                    {
                        _payment = LoanMethod.Payment(_payhash, _loanDate, _pre_nextrepaymentdate,propObj.SingleRepayment[rep], _interest_before_advance, _pre_Balance);
                        if (propObj.applypayhash.Length-1 > rep)
                        {
                            rep += 1;
                        }
                    }
                    else if (IsSinglePayment && _MaxPayHash < lastchangespayhash && Previous_Repayment != 0)
                    {
                        _payment = LoanMethod.Payment(_payhash, _loanDate, _pre_nextrepaymentdate, Previous_Repayment, _interest_before_advance, _pre_Balance);
                    }
                    else
                    {
                        _payment = LoanMethod.Payment(_payhash, _loanDate, _pre_nextrepaymentdate, RePayment, _interest_before_advance, _pre_Balance);
                    }
                    double _balance = LoanMethod.Balance(_pre_Balance, _payment, _interest_before_advance, advCharges);
                    r = dt.NewRow();
                    r[0] = extraday;
                    string _nextInterestDate = LoanMethod.NextInterestDate(_loanDate, _payhash, balance);
                    if (_nextInterestDate == "")
                    {
                        r[1] = DBNull.Value;
                    }
                    else
                    {
                        r[1] = _nextInterestDate;
                    }
                    string _nextRepaymentDate = LoanMethod.NextReapymentDate(propObj.Frequency, _loanDate, propObj.FirstRepaymentDate, _pre_nextrepaymentdate, _pre_nextinterestdate, _nextInterestDate, _balance);
                    if (_nextRepaymentDate == "")
                    {
                        r[2] = DBNull.Value;
                    }
                    else
                    {
                        r[2] = _nextRepaymentDate;
                    }
                    r[3] = _payhash;
                    if (_loanDate == "")
                    {
                        r[4] = DBNull.Value;
                    }
                    else
                    {
                        r[4] = _loanDate;
                    }
                    r[5] = _payment;
                    r[6] = _interest_before_advance;
                    r[7] = advCharges;
                    r[8] = _balance;
                    r[9] = _interest;
                    r[10] = LoanMethod.DiscFactor(_payhash, trailRate / 100);
                    dt.Rows.Add(r);
                    balance = _balance;
                }
                else {
                    balance = 0;
                }
            }
            return dt;
        }
        /// <summary>
        /// Calculate difference value
        /// </summary>
        /// <param name="dt">loan table.(For loan table call laon table method of loan method class.)</param>
        /// <param name="propObj">loan propertie class object(see loan properties class)</param>
        /// <param name="advCharges">total funded amount/advnace charges</param>
        /// <returns>difference value as double datatype</returns>
        public static double Cal_Difference(DataTable dt, LoanProperties propObj,double advCharges,int NoofInstallment=0)
        {
            double _preInterestRate = 0;
            double _balance = 0;
            double _pv_EMI = 0;
            double _diff = 0;
            if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        try
                        {
                            _pv_EMI +=Convert.ToDouble(dt.Rows[i]["payment"]) * Convert.ToDouble(dt.Rows[i]["discFator"]);
                        }
                        catch (Exception)
                        {
                            _pv_EMI += 0;
                        }
                        double _temp_interest = 0;
                        if (Convert.ToDateTime(dt.Rows[i]["date"]) <= Convert.ToDateTime(propObj.FirstRepaymentDate))
                        {
                            try
                            {
                                _temp_interest = Convert.ToDouble(dt.Rows[i]["Interest"]);
                            }
                            catch (Exception)
                            {
                                _temp_interest += 0;
                            }
                        }
                        if (Convert.ToString(dt.Rows[i]["Payhash"]) == LoanMethod.No_of_Installments(propObj.LoanDuration, propObj.Frequency,NoofInstallment).ToString())
                        {
                            try
                            {
                                _balance = Convert.ToDouble(dt.Rows[i]["balance"]);
                            }
                            catch (Exception)
                            {
                                _balance += 0;
                            }
                        }
                        _preInterestRate += _temp_interest;
                    }
                    DateTime _tempFirstRepaymentDate = Convert.ToDateTime(propObj.FirstRepaymentDate);
                    DateTime _tempLoanDate = Convert.ToDateTime(propObj.FirstRepaymentDate);
                    _preInterestRate += (propObj.MonthlyFee * ((_tempFirstRepaymentDate.Month - _tempLoanDate.Month + 1 + _tempFirstRepaymentDate.Year - _tempLoanDate.Year)) * 12);
                    //=ROUND(PMT(I4/D5,I11,-Q17-I7,,1),2)+N13*3
                    _diff = Math.Round(_pv_EMI - propObj.Principal,4);
                }
            return _diff;
        }
      /// <summary>
      /// Returns a datatable with calculated value of 
      /// Difference,PreInterestRate,Pv_EMI,Balance,LastRepaymentDate
      /// NoofInstallment,TotalAmountFunded,Repayment
      /// RepaymentLastInstallment,InitialInstallment,SimpleInterest
      /// and ratechange datatable
      /// </summary>
      /// <param name="dt">loan table.(For loan table call laon table method of loan method class.)</param>
      /// <param name="propObj">loan propertie class object(see loan properties class)</param>
      /// <param name="advCharges">total funded amount/advnace charges</param>
      /// <param name="Repayment">Calculated Repayment value</param>
        /// <param name="Interest">Calculated interest value</param>
      /// <returns>datatble</returns>
        public static DataSet Cal_Difference_dataTable(DataTable dt, LoanProperties propObj, double advCharges,double Repayment,double Interest,string DateFormat="",int NoofInstallment=0,Boolean IsOutput=false)
        {
            DataSet _dsDifference = new DataSet(); 
            DataTable _dtAllValue = new DataTable();
            DataTable _dtRateChange = new DataTable();
            DataTable _dtOutput = new DataTable();
            DataTable _payment = new DataTable();
            _dtRateChange.Columns.Add("Description");
            _dtRateChange.Columns.Add("ChangeRate");
            _dtRateChange.Columns.Add("ChangeFreq");
            _dtRateChange.Columns.Add("ChangeRepmt");
            _dtRateChange.Columns.Add("SinglePmt");

            _dtOutput.Columns.Add("CurrentBalance");
            _dtOutput.Columns.Add("InterestRate");
            _dtOutput.Columns.Add("RepaymentFrequency");
            _dtOutput.Columns.Add("RepaymentAmount");
            _dtOutput.Columns.Add("RepaymentRemaining");

            _payment.Columns.Add("CurrentBalance");
            _payment.Columns.Add("InterestRate");
            _payment.Columns.Add("RepaymentFrequency");
            _payment.Columns.Add("RepaymentAmount");
            _payment.Columns.Add("RepaymentRemaining"); 

            _dtAllValue.Columns.Add("Difference");
            _dtAllValue.Columns.Add("PreInterestRate");
            _dtAllValue.Columns.Add("Pv_EMI");
            _dtAllValue.Columns.Add("Balance");
            _dtAllValue.Columns.Add("LastRepaymentDate");
            _dtAllValue.Columns.Add("NoofInstallment");
            _dtAllValue.Columns.Add("TotalAmountFunded");
         //   _dtAllValue.Columns.Add("NoofInstallment");
            _dtAllValue.Columns.Add("Repayment");
            _dtAllValue.Columns.Add("RepaymentLastInstallment");
            _dtAllValue.Columns.Add("InitialInstallment");
            _dtAllValue.Columns.Add("SimpleInterest");
             
            //
            //_dtAllValue.Columns.Add("RepaymentRemainingFromToday");

            ////Last Transaction Date

            //_dtAllValue.Columns.Add("crLasttransactiondate");
            //_dtAllValue.Columns.Add("cfLasttransactiondate");
            //_dtAllValue.Columns.Add("cLasttransactiondate");
            //_dtAllValue.Columns.Add("csLasttransactiondate");

            ////Last Transaction Date Balance

            //_dtAllValue.Columns.Add("crLastTransDateBalance");
            //_dtAllValue.Columns.Add("cfLastTransDateBalance");
            //_dtAllValue.Columns.Add("cLastTransDateBalance");
            //_dtAllValue.Columns.Add("csLastTransDateBalance");

            double _preInterestRate = 0;
            double _balance = 0;
            double _pv_EMI = 0;
            double _diff = 0;
            string _payhash = "";
            double _last_repayment_installment = 0;
            double _count = 0;
            //Last Transaction Date
            string _crlasttransactiondate = "";
            string _cflasttransactiondate = "";
            string _clasttransactiondate = "";
            string _cslasttransactiondate = "";

            //Last Transaction Date Balance
            double _crLastTransDateBalance = 0;
            double _cfLastTransDateBalance = 0;
            double _cLastTransDateBalance = 0;
            double _csLastTransDateBalance = 0;

            //Interest since Last Transaction Date 
            string _crInterestLastTransDate = "";
            string _cfInterestLastTransDate = "";
            string _cInterestLastTransDate = "";
            string _csInterestLastTransDate = "";

            string _lastPayment = "0";
            //Balance as on date
            string _crNewFirstRepaymentDate = "";
            string _cfNewFirstRepaymentDate = "";
            string _cNewFirstRepaymentDate = "";
            string _csNewFirstRepaymentDate = "";

            //Term Remianing
            double _crTermRemaing = 0;
            double _cfTermRemaing = 0;
            double _cTermRemaing = 0;
            double _csTermRemaing = 0;

            //Last Tx date before Effective Date

            string _crLastTxEffectiveDate = "";
            string _cfLastTxEffectiveDate = "";
            string _cLastTxEffectiveDate = "";
            string _csLastTxEffectiveDate = "";

            //Balance as on last Tx date
            double _crBalanceonlasttransactiondate = 0;
            double _cfBalanceonlasttransactiondate = 0;
            double _cBalanceonlasttransactiondate = 0;
            double _csBalanceonlasttransactiondate = 0;

            //No. of Repayments Made (M2)

            int _crcount_of_Repayment_Made=0;
            int _cfcount_of_Repayment_Made=0;
            int _ccount_of_Repayment_Made=0;

            //Time Lapsed up to last TX Date

            int _crTime_Lapsed_count = 0;
            int _cfTime_Lapsed_count = 0;
            int _cTime_Lapsed_count = 0;
            int _csTime_Lapsed_count = 0;

            //Accrued Interest Reduction
            //=1/(1+(Q7-EOMONTH(Q12,-1)-1)*'Front-end'!$O$4/365)
            double _crsof_interest = 0;
            double _cfsof_interest = 0;
            double _csof_interest = 0;
            double _cssof_interest = 0;

            //sinlge payment no of installment remaining
            int _csnoofinstallmentreaminging = 0;


            string _lastrepaymentdate= LastRepaymentDate(propObj.LoanDuration, propObj.Frequency, propObj.FirstRepaymentDate,NoofInstallment);
            int _noofinstallment=No_of_Installments(propObj.LoanDuration, propObj.Frequency,NoofInstallment);  
            if (dt.Rows.Count > 0)
            {
                try
                {
                    _crNewFirstRepaymentDate = _cfNewFirstRepaymentDate = _cNewFirstRepaymentDate = (from r in dt.AsEnumerable()
                                                                                                     select new { d = r.Field<DateTime>("date"), ph = r.Field<string>("payhash") }
                                ).Where(p => IsNumeric(p.ph) == true && p.d > DateTime.Today)
                                 .FirstOrDefault().d.ToString();
                }
                catch (Exception) { if (propObj.NewRepaymentDate != "") { _crNewFirstRepaymentDate = _cfNewFirstRepaymentDate = _cNewFirstRepaymentDate = propObj.NewRepaymentDate; } else { _crNewFirstRepaymentDate = _cfNewFirstRepaymentDate = _cNewFirstRepaymentDate = Convert.ToString(dt.Rows[dt.Rows.Count - 1]["date"]); } }
                DateTime _dtTemp = DateTime.Today.AddDays(-NoofDays(propObj.Frequency) * 3);   
                try
                {
                    _csNewFirstRepaymentDate = Convert.ToString((from r in dt.AsEnumerable()
                                                                 select new { d = r.Field<DateTime>("date"), ph = r.Field<string>("payhash") }
                               ).Where(p => IsNumeric(p.ph) == true && p.d>_dtTemp)
                                .FirstOrDefault().d);
                }
                catch (Exception) { }

                try
                {
                    string _temp_LoanDate = "";
                    DataColumn col = dt.Columns["date"];
                    DataTable tbl = col.Table;
                     _temp_LoanDate = tbl.AsEnumerable()
                    .Where(p => p.Field<DateTime>(col.ColumnName)< Convert.ToDateTime(ReturnMaxDate(_crNewFirstRepaymentDate, propObj.LoanDate)))
                    .Select(cols => cols.Field<DateTime>(col.ColumnName))
                    .OrderByDescending(p => p.Ticks)
                    .FirstOrDefault().ToString();
                    //_temp_LoanDate = (from r in dt.AsEnumerable()
                    //         select new { d = r.Field<DateTime>("date")}
                    //            ).Where(p =>p.d<Convert.ToDateTime(ReturnMaxDate(_crNewFirstRepaymentDate, propObj.LoanDate))).ToString();
                    _crLastTxEffectiveDate=_cfLastTxEffectiveDate=_cLastTxEffectiveDate= ReturnMaxDate(_temp_LoanDate, propObj.LoanDate);
                }
                catch (Exception) {}
                try
                {
                    string _temp_LoanDate = "";
                    DataColumn col = dt.Columns["date"];
                    DataTable tbl = col.Table;
                    _temp_LoanDate = tbl.AsEnumerable()
                   .Where(p => p.Field<DateTime>(col.ColumnName) < Convert.ToDateTime(ReturnMaxDate(_crNewFirstRepaymentDate, propObj.LoanDate)))
                   .Select(cols => cols.Field<DateTime>(col.ColumnName))
                   .OrderByDescending(p => p.Ticks)
                   .FirstOrDefault().ToString();
                    _csLastTxEffectiveDate= ReturnMaxDate(_temp_LoanDate, propObj.LoanDate);
                }
                catch (Exception) { }
                try
                {
                    DataColumn col = dt.Columns["date"];
                    DataTable tbl = col.Table;
                    _crlasttransactiondate = tbl.AsEnumerable()
                   .Where(p => p.Field<DateTime>(col.ColumnName) < DateTime.Today)
                   .Select(cols => cols.Field<DateTime>(col.ColumnName))
                   .OrderByDescending(p => p.Ticks)
                   .FirstOrDefault().ToString();
                    //if (Convert.ToDateTime(dt.Rows[i]["date"]) < DateTime.Today)
                    //{
                    //    _crlasttransactiondate = Convert.ToString(dt.Rows[i]["date"]);
                    //}
                }
                catch (Exception)
                {

                }
                //=MAX(DMAX($H$17:$I$515,"Date",$AA$3:$AB$4),$I$5)
                try
                {
                    DataColumn col = dt.Columns["date"];
                    DataTable tbl = col.Table;
                    _cflasttransactiondate = tbl.AsEnumerable()
                   .Where(p => p.Field<DateTime>(col.ColumnName) < DateTime.Today && p.Field<double>("payment")>0)
                   .Select(cols => cols.Field<DateTime>(col.ColumnName))
                   .OrderByDescending(p => p.Ticks)
                   .FirstOrDefault().ToString();

                    //if (Convert.ToDateTime(dt.Rows[i]["date"]) < DateTime.Today && Convert.ToDouble(dt.Rows[i]["payment"]) > 0)
                    //{
                    //    _cflasttransactiondate = Convert.ToString(dt.Rows[i]["date"]);
                    //}

                }
                catch (Exception)
                {
                }

                //=MAX(DMAX($H$17:$H$515,"Date",$AA$3:$AB$4),$I$5) && =MAX(DMAX($H$17:$H$515,"Date",$AA$3:$AB$4),$I$5)
                _cslasttransactiondate = _clasttransactiondate = propObj.LoanDate;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    
                    try
                    {
                        _pv_EMI += Convert.ToDouble(dt.Rows[i]["payment"]) * Convert.ToDouble(dt.Rows[i]["discFator"]);
                    }
                    catch (Exception)
                    {
                        _pv_EMI += 0;
                    }
                    if (IsNumeric(dt.Rows[i]["Payhash"]))
                    {
                        _payhash = dt.Rows[i]["Payhash"].ToString();
                        if(IsOutput)
                        {
                            DataRow _dr = _payment.NewRow();
                            _dr[0] = dt.Rows[i]["Payhash"].ToString();
                            _dr[1] = "";
                            _dr[2] = CDate(dt.Rows[i]["date"].ToString(), DateFormat);
                            _dr[3] = "";
                            _dr[4] = dt.Rows[i]["payment"].ToString();
                            if (propObj.LoanType.ToLower()  == "changesinglepayment")
                            {
                                if (Convert.ToDateTime(dt.Rows[i]["date"]) >=Convert.ToDateTime(_csNewFirstRepaymentDate))
                                {
                                    _payment.Rows.Add(_dr);
                                }
                            }
                            else {
                                _payment.Rows.Add(_dr);
                            }
                        }
                    }
                    if (_payhash == Convert.ToString(_noofinstallment))
                    {
                        try
                        {
                            _last_repayment_installment = Repayment - Convert.ToDouble(dt.Rows[i]["payment"]);
                            _lastPayment = Convert.ToString(dt.Rows[i]["payment"]);
                        }
                        catch (Exception)
                        {
                            _last_repayment_installment = 0;
                        }
                    }
                    double _temp_interest = 0;
                    if (Convert.ToDateTime(dt.Rows[i]["date"]) <= Convert.ToDateTime(propObj.FirstRepaymentDate))
                    {
                        try
                        {
                            _temp_interest += Convert.ToDouble(dt.Rows[i]["Interest"]);
                        }
                        catch (Exception)
                        {
                            _temp_interest += 0;
                        }
                    }
                    if (Convert.ToString(dt.Rows[i]["Payhash"]) == LoanMethod.No_of_Installments(propObj.LoanDuration, propObj.Frequency).ToString())
                    {
                        try
                        {
                            _balance += Convert.ToDouble(dt.Rows[i]["balance"]);
                        }
                        catch (Exception)
                        {
                            _balance += 0;
                        }
                    }
                    _preInterestRate += _temp_interest;
                    //=COUNTIFS($H$18:$H$515,">="&TODAY(),$G$18:$G$515,">"&"0")

                    if (Convert.ToDateTime(dt.Rows[i]["date"]) >= DateTime.Today)
                    {
                        if(IsNumeric(dt.Rows[i]["Payhash"]))
                        {
                            _count += 1;
                        }
                    }


                    //if (Convert.ToDateTime(dt.Rows[i]["date"]) <= DateTime.Today)
                    //{
                    //    if (IsNumeric(dt.Rows[i]["Payhash"]))
                    //    {
                    //        try
                    //        {
                    //            _crNewFirstRepaymentDate =_cfNewFirstRepaymentDate=_cNewFirstRepaymentDate= Convert.ToString(dt.Rows[i + 1]["date"]);
                                
                    //        }
                    //        catch (Exception) { }
                    //    }
                    //}
                    //if (IsNumeric(dt.Rows[i]["Payhash"]))
                    //{
                    //    if (Convert.ToInt32(dt.Rows[i]["Payhash"]) == 1)
                    //    {
                    //        _csNewFirstRepaymentDate = Convert.ToString(dt.Rows[i]["date"]);
                    //    }
                    //}

                    //=MAX(DMAX($H$17:$H$515,"Date",AA3:AA4),$I$5)
                    //if (Convert.ToDateTime(dt.Rows[i]["date"]) < DateTime.Today)
                    //{
                    //    _crlasttransactiondate = Convert.ToString(dt.Rows[i]["date"]);
                    //}
                    ////=MAX(DMAX($H$17:$I$515,"Date",$AA$3:$AB$4),$I$5)
                    //try
                    //{
                    //    if (Convert.ToDateTime(dt.Rows[i]["date"]) < DateTime.Today && Convert.ToDouble(dt.Rows[i]["payment"]) > 0)
                    //    {
                    //        _cflasttransactiondate = Convert.ToString(dt.Rows[i]["date"]);
                    //    }
                   
                    //}
                    //catch (Exception) { 
                    //}

                    //=MAX(DMAX($H$17:$H$515,"Date",$AA$3:$AB$4),$I$5) && =MAX(DMAX($H$17:$H$515,"Date",$AA$3:$AB$4),$I$5)
                    _cslasttransactiondate = _clasttransactiondate = propObj.LoanDate;

                    //=SUMIF($H$18:$L$511,Q3,$L$18:$L$515)+SUMIF($H$18:$H$515,Q3,$I$18:$I$19)
                   
                    if (EqualDate(Convert.ToString(dt.Rows[i]["date"]), _crlasttransactiondate))
                    {
                        try
                        {
                            _crLastTransDateBalance = (Convert.ToDouble(dt.Rows[i]["balance"]) + Convert.ToDouble(dt.Rows[i]["payment"]));
                        }
                        catch (Exception) { 
                        }
                    }
                    if (EqualDate(Convert.ToString(dt.Rows[i]["date"]), _cflasttransactiondate))
                    {
                        try
                        {
                            _cfLastTransDateBalance = Convert.ToDouble(dt.Rows[i]["balance"]);
                        }
                        catch (Exception) { }
                    }
                    if (EqualDate(Convert.ToString(dt.Rows[i]["date"]), _clasttransactiondate))
                    {
                        try
                        {
                            _cLastTransDateBalance = (Convert.ToDouble(dt.Rows[i]["balance"]) + Convert.ToDouble(dt.Rows[i]["payment"]));
                        }
                        catch (Exception) { }
                    }
                    if (EqualDate(Convert.ToString(dt.Rows[i]["date"]), _cslasttransactiondate))
                    {
                        try
                        {
                            _csLastTransDateBalance = (Convert.ToDouble(dt.Rows[i]["balance"]) + Convert.ToDouble(dt.Rows[i]["payment"]));
                        }
                        catch (Exception) { }
                    }

                    _crInterestLastTransDate = InterestSinceLastTransaction(_crlasttransactiondate, 365, (propObj.InterestRate / 100), _crLastTransDateBalance);
                    _cfInterestLastTransDate = InterestSinceLastTransaction(_cflasttransactiondate, 365, (propObj.InterestRate / 100),_cfLastTransDateBalance);
                    _cInterestLastTransDate = InterestSinceLastTransaction(_clasttransactiondate, 365, (propObj.InterestRate / 100),_cLastTransDateBalance);
                    _csInterestLastTransDate = InterestSinceLastTransaction(_cslasttransactiondate, 365, (propObj.InterestRate / 100), _csLastTransDateBalance);

                    _crTermRemaing = TermRemainingFromToday(propObj.FirstRepaymentDate, propObj.LoanDuration, _crNewFirstRepaymentDate, propObj.LoanDate);
                    _cfTermRemaing = TermRemainingFromToday(propObj.FirstRepaymentDate, propObj.LoanDuration, _cfNewFirstRepaymentDate, propObj.LoanDate);
                    _cTermRemaing = TermRemainingFromToday(propObj.FirstRepaymentDate, propObj.LoanDuration, _cNewFirstRepaymentDate, propObj.LoanDate);
                    _csTermRemaing = TermRemainingFromToday(propObj.FirstRepaymentDate, propObj.LoanDuration, _csNewFirstRepaymentDate, propObj.LoanDate);
                    
                    //Last Tx Effective Date
                    //try
                    //{
                    //    if (Convert.ToDateTime(dt.Rows[i]["date"]) < Convert.ToDateTime(ReturnMaxDate(_crNewFirstRepaymentDate, propObj.LoanDate)))
                    //    {
                    //        _crLastTxEffectiveDate = ReturnMaxDate(Convert.ToString(dt.Rows[i]["date"]), propObj.LoanDate);
                    //    }
                    //}
                    //catch (Exception) {}
                    //try
                    //{
                    //    if (Convert.ToDateTime(dt.Rows[i]["date"]) < Convert.ToDateTime(ReturnMaxDate(_cfNewFirstRepaymentDate, propObj.LoanDate)))
                    //    {
                    //        _cfLastTxEffectiveDate = ReturnMaxDate(Convert.ToString(dt.Rows[i]["date"]), propObj.LoanDate);
                    //    }
                    //}
                    //catch (Exception) { }
                    //try
                    //{
                    //    if (Convert.ToDateTime(dt.Rows[i]["date"]) < Convert.ToDateTime(ReturnMaxDate(_cNewFirstRepaymentDate, propObj.LoanDate)))
                    //    {
                    //        _cLastTxEffectiveDate = ReturnMaxDate(Convert.ToString(dt.Rows[i]["date"]), propObj.LoanDate);
                    //    }
                    //}
                    //catch (Exception){}
                    //try
                    //{
                    //    if (Convert.ToDateTime(dt.Rows[i]["date"]) < Convert.ToDateTime(ReturnMaxDate(_csNewFirstRepaymentDate, propObj.LoanDate)))
                    //    {
                    //        _csLastTxEffectiveDate = ReturnMaxDate(Convert.ToString(dt.Rows[i]["date"]), propObj.LoanDate);
                    //    }
                    //}
                    //catch (Exception) {}

                     //Balance as on last Tx date
                    if (EqualDate(Convert.ToString(dt.Rows[i]["date"]),_crLastTxEffectiveDate))
                    {
                        //=SUMIF($H$18:$L$511,Q12,$L$18:$L$515)+SUMIF($H$18:$H$515,Q12,$I$18:$I$19)*(Q12=Q7)
                        try
                        {
                            _crBalanceonlasttransactiondate = Convert.ToDouble(dt.Rows[i]["balance"]);
                            if (EqualDate(_crLastTxEffectiveDate, _crNewFirstRepaymentDate))
                            {
                                _crBalanceonlasttransactiondate += Convert.ToDouble(dt.Rows[i]["payment"]);
                            }
                        }
                        catch (Exception) { 
                        }
                    }

                    if (EqualDate(Convert.ToString(dt.Rows[i]["date"]), _cfLastTxEffectiveDate))
                    {
                        //=SUMIF($H$18:$L$511,Q12,$L$18:$L$515)+SUMIF($H$18:$H$515,Q12,$I$18:$I$19)
                        try
                        {
                            _cfBalanceonlasttransactiondate = Convert.ToDouble(dt.Rows[i]["balance"]);
                            if (EqualDate(_cfLastTxEffectiveDate, _crNewFirstRepaymentDate))
                            {
                                _cfBalanceonlasttransactiondate += Convert.ToDouble(dt.Rows[i]["payment"]);
                            }
                        }
                        catch (Exception)
                        {
                        }
                    }

                    if (EqualDate(Convert.ToString(dt.Rows[i]["date"]), _cLastTxEffectiveDate))
                    {
                        //=SUMIF($H$18:$L$511,Q12,$L$18:$L$515)+SUMIF($H$18:$H$515,Q12,$I$18:$I$19)
                        try
                        {
                            _cBalanceonlasttransactiondate = Convert.ToDouble(dt.Rows[i]["balance"]);
                            if (EqualDate(_cLastTxEffectiveDate, _crNewFirstRepaymentDate))
                            {
                                _cBalanceonlasttransactiondate += Convert.ToDouble(dt.Rows[i]["payment"]);
                            }
                        }
                        catch (Exception)
                        {
                        }
                    }
                    if (EqualDate(Convert.ToString(dt.Rows[i]["date"]), _csLastTxEffectiveDate))
                    {
                        //=SUMIF($H$18:$L$511,Q12,$L$18:$L$515)+SUMIF($H$18:$H$515,Q12,$I$18:$I$19)
                        try
                        {
                            _csBalanceonlasttransactiondate = Convert.ToDouble(dt.Rows[i]["balance"]);
                            if (EqualDate(_csLastTxEffectiveDate, _crNewFirstRepaymentDate))
                            {
                                _csBalanceonlasttransactiondate += Convert.ToDouble(dt.Rows[i]["payment"]);
                            }
                        }
                        catch (Exception)
                        {
                        }
                    }
                    if(EqualDate(Convert.ToString(dt.Rows[i]["date"]), _csNewFirstRepaymentDate))
                    {
                        try{
                            _csnoofinstallmentreaminging = Convert.ToInt32(dt.Rows[i]["Payhash"]);
                        }catch(Exception){}
                    }
                      //Interest from Last Tx Date
                    //1
                    try
                    {
                        DateTime dtTemp=Convert.ToDateTime(_crLastTxEffectiveDate).AddMonths(-1);
                        if (Convert.ToDateTime(dt.Rows[i]["date"]) > Convert.ToDateTime(getEOM(dtTemp.ToString())) && Convert.ToDateTime(dt.Rows[i]["date"]) <= Convert.ToDateTime(_crLastTxEffectiveDate))
                        {
                            _crsof_interest += Convert.ToDouble(dt.Rows[i]["interest"]); 
                        }
                    }
                    catch (Exception) { 
                    }

                    //2
                    try
                    {
                        DateTime dtTemp = Convert.ToDateTime(_cfLastTxEffectiveDate).AddMonths(-1);
                        if (Convert.ToDateTime(dt.Rows[i]["date"]) > Convert.ToDateTime(getEOM(dtTemp.ToString())) && Convert.ToDateTime(dt.Rows[i]["date"]) <= Convert.ToDateTime(_cfLastTxEffectiveDate))
                        {
                            _cfsof_interest += Convert.ToDouble(dt.Rows[i]["interest"]);
                        }
                    }
                    catch (Exception)
                    {
                    }
                    //3
                    try
                    {
                        DateTime dtTemp = Convert.ToDateTime(_cLastTxEffectiveDate).AddMonths(-1);
                        if (Convert.ToDateTime(dt.Rows[i]["date"]) > Convert.ToDateTime(getEOM(dtTemp.ToString())) && Convert.ToDateTime(dt.Rows[i]["date"]) <= Convert.ToDateTime(_cLastTxEffectiveDate))
                        {
                            _csof_interest += Convert.ToDouble(dt.Rows[i]["interest"]);
                        }
                    }
                    catch (Exception)
                    {
                    }

                    //4
                    try
                    {
                        DateTime dtTemp = Convert.ToDateTime(_csLastTxEffectiveDate).AddMonths(-1);
                        if (Convert.ToDateTime(dt.Rows[i]["date"]) > Convert.ToDateTime(getEOM(dtTemp.ToString())) && Convert.ToDateTime(dt.Rows[i]["date"]) <= Convert.ToDateTime(_csLastTxEffectiveDate))
                        {
                            _cssof_interest += Convert.ToDouble(dt.Rows[i]["interest"]);
                        }
                    }
                    catch (Exception)
                    {
                    }

                    //No. of Repayments Made (M2)

                    //=COUNTIFS($H$18:$H$515,"<"&'Front-end'!V3,Calc!$I$18:$I$515,">"&0)
                    try{
                    if(Convert.ToDateTime(dt.Rows[i]["date"])<Convert.ToDateTime(_crNewFirstRepaymentDate) && Convert.ToDouble(dt.Rows[i]["Payment"])>0)
                    {
                        _crcount_of_Repayment_Made+=1;
                    }
                    }catch(Exception){
                    }

                    //=COUNTIFS($H$18:$H$515,"<"&R7,Calc!$I$18:$I$515,">"&0)
                    try{
                    if(Convert.ToDateTime(dt.Rows[i]["date"])<Convert.ToDateTime(_cfNewFirstRepaymentDate) && Convert.ToDouble(dt.Rows[i]["Payment"])>0)
                    {
                        _cfcount_of_Repayment_Made+=1;
                    }
                    }catch(Exception){
                    }

                    //=COUNTIFS($H$18:$H$515,"<"&R7,Calc!$I$18:$I$515,">"&0)
                    try{
                    if(Convert.ToDateTime(dt.Rows[i]["date"])<=(Convert.ToDateTime(_cNewFirstRepaymentDate).AddDays(-1)) && Convert.ToDouble(dt.Rows[i]["Payment"])>0)
                    {
                        _ccount_of_Repayment_Made+=1;
                    }
                    }catch(Exception){
                    }

                    //=COUNTIFS($H$18:$H$515,"<"&R7,Calc!$I$18:$I$515,">"&0)
                    //try{
                    //if(Convert.ToDateTime(dt.Rows[i]["date"])<0 && Convert.ToDouble(dt.Rows[i]["Payment"])>0)
                    //{
                    //    _cfcount_of_Repayment_Made+=1;
                    //}
                    //}catch(Exception){
                    //}

                    
                    //////=ROUNDUP(COUNTIFS($H$18:$H$515,"<="&Q12,$I$18:$I$515,">"&0)*IF($I$1=$B$1,1,IF($I$1=$B$2,2,4.3)),0)
                    //if (Convert.ToDateTime(dt.Rows[i]["date"]) <= Convert.ToDateTime(_crLastTxEffectiveDate) && Convert.ToDouble(dt.Rows[i]["payment"])>0)
                    //{
                        
                    //}
                }
                //Time Lapsed up to last TX Date
                _crTime_Lapsed_count = (from r in dt.AsEnumerable()
                         select new {d = r.Field<DateTime>("date"), ph = r.Field<string>("payhash") }
                          ).Where(p => ReturnIfNumeric(p.ph)>0 && p.d <= Convert.ToDateTime(_crLastTxEffectiveDate))
                           .Count();

                _cfTime_Lapsed_count = (from r in dt.AsEnumerable()
                                        select new { d = r.Field<DateTime>("date"), ph = r.Field<string>("payhash") }
                          ).Where(p => ReturnIfNumeric(p.ph) > 0 && p.d <= Convert.ToDateTime(_cfLastTxEffectiveDate))
                           .Count();

                _cTime_Lapsed_count = (from r in dt.AsEnumerable()
                                        select new { d = r.Field<DateTime>("date"), ph = r.Field<string>("payhash") }
                          ).Where(p => ReturnIfNumeric(p.ph) > 0 && p.d <= Convert.ToDateTime(_cLastTxEffectiveDate))
                           .Count();

                _csTime_Lapsed_count = (from r in dt.AsEnumerable()
                                        select new { d = r.Field<DateTime>("date"), ph = r.Field<string>("payhash") }
                          ).Where(p => ReturnIfNumeric(p.ph) > 0 && p.d <= Convert.ToDateTime(_csLastTxEffectiveDate))
                           .Count();

              
                DateTime _tempFirstRepaymentDate = Convert.ToDateTime(propObj.FirstRepaymentDate);
                DateTime _tempLoanDate = Convert.ToDateTime(propObj.FirstRepaymentDate);
                _preInterestRate += (propObj.MonthlyFee * ((_tempFirstRepaymentDate.Month - _tempLoanDate.Month + 1 + ((_tempFirstRepaymentDate.Year - _tempLoanDate.Year)*12))));
                //=ROUND(PMT(I4/D5,I11,-Q17-I7,,1),2)+N13*3
                _diff = Math.Round(_pv_EMI - propObj.Principal, 4);
            }
            DataRow dr =_dtAllValue.NewRow();
            dr[0] = _diff;
            dr[1] = _preInterestRate;
            dr[2] = _pv_EMI;
            dr[3] = _balance;
            dr[4] = CDate(_lastrepaymentdate,DateFormat);
            dr[5] = _noofinstallment;
            dr[6] = advCharges;
           // dr[7] = ;
            dr[7] = Repayment;
            dr[8] = _last_repayment_installment;
            dr[9] = InitialInstallment(Interest, propObj.Frequency, propObj.LoanDuration, advCharges, _preInterestRate, propObj.MonthlyFee);
            dr[10] = SimpleInterest(propObj.LoanDate, propObj.FirstRepaymentDate, Interest, advCharges);
            _dtAllValue.Rows.Add(dr);

            DataRow _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "# Repayments Remaining from today";
            _drRateChange[1] = _drRateChange[2] = _drRateChange[3] = _drRateChange[4] = _count;
            _dtRateChange.Rows.Add(_drRateChange);

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Last transaction date";
            _drRateChange[1] = CDate(_crlasttransactiondate,DateFormat);
            _drRateChange[2] = CDate(_cflasttransactiondate,DateFormat);
            _drRateChange[3] = _drRateChange[4] = CDate(_clasttransactiondate,DateFormat);
            _dtRateChange.Rows.Add(_drRateChange);

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Balance as on last Tx date";
            _drRateChange[1] = Math.Round(_crLastTransDateBalance);
            _drRateChange[2] = Math.Round(_cfLastTransDateBalance);
            _drRateChange[3] = Math.Round(_cLastTransDateBalance);
            _drRateChange[4] = Math.Round(_csLastTransDateBalance);
            _dtRateChange.Rows.Add(_drRateChange);

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Interest Since last Tx date";
            _drRateChange[1] =_crInterestLastTransDate;
            _drRateChange[2] =_cfInterestLastTransDate;
            _drRateChange[3] = _cInterestLastTransDate;
            _drRateChange[4] = _csInterestLastTransDate;
            _dtRateChange.Rows.Add(_drRateChange);

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Balance as on date";
            _drRateChange[1] = Convert.ToDouble(_crInterestLastTransDate)+Convert.ToDouble(_crLastTransDateBalance);
            _drRateChange[2] = Convert.ToDouble(_cfInterestLastTransDate)+Convert.ToDouble(_cfLastTransDateBalance);
            _drRateChange[3] = Convert.ToDouble(_cInterestLastTransDate)+Convert.ToDouble(_cLastTransDateBalance);
            _drRateChange[4] = Convert.ToDouble(_csInterestLastTransDate) + Convert.ToDouble(_csLastTransDateBalance);
            _dtRateChange.Rows.Add(_drRateChange);

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "New First Repayment Date";
            _drRateChange[1] =CDate(_crNewFirstRepaymentDate,DateFormat);
            _drRateChange[2] =CDate(_cfNewFirstRepaymentDate,DateFormat);
            _drRateChange[3] =CDate(_cNewFirstRepaymentDate,DateFormat);
            _drRateChange[4] =CDate(_csNewFirstRepaymentDate,DateFormat);
            _dtRateChange.Rows.Add(_drRateChange);
            
            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Last Repayment Date";
            _drRateChange[1] = _drRateChange[2] = _drRateChange[3] = _drRateChange[4] = CDate(_lastrepaymentdate,DateFormat);
            _dtRateChange.Rows.Add(_drRateChange);

             _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Last Repayment Amount";
            _drRateChange[1] =_drRateChange[2]=_drRateChange[3]=_drRateChange[4]=_lastPayment;
            _dtRateChange.Rows.Add(_drRateChange);

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "No. of Installments Remaining";
            _drRateChange[1] = _drRateChange[2] = _drRateChange[3] = _drRateChange[4] = _count;
            _dtRateChange.Rows.Add(_drRateChange);




            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Term Remaining from today";
            _drRateChange[1] = _crTermRemaing;
            _drRateChange[2] = "";
            _drRateChange[3] = _cTermRemaing;
            _drRateChange[4] = _csTermRemaing;
            _dtRateChange.Rows.Add(_drRateChange);

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Last Tx date before Effective Date";
            _drRateChange[1] = CDate(_crLastTxEffectiveDate,DateFormat);
            _drRateChange[2] = CDate(_cfLastTxEffectiveDate,DateFormat);
            _drRateChange[3] = CDate(_cLastTxEffectiveDate,DateFormat);
            _drRateChange[4] = CDate(_csLastTxEffectiveDate,DateFormat);
            _dtRateChange.Rows.Add(_drRateChange);

                //Balance as on last Tx date
            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Balance as on last Tx date.";
            _drRateChange[1] = _crBalanceonlasttransactiondate;
            _drRateChange[2] = _cfBalanceonlasttransactiondate;
            _drRateChange[3] = _cBalanceonlasttransactiondate;
            _drRateChange[4] = _csBalanceonlasttransactiondate;
            _dtRateChange.Rows.Add(_drRateChange);

            //Last Tx Date is for
            string _crLasttxDateFor = LastTxDateFor(_crLastTxEffectiveDate);
            string _cfLasttxDateFor = LastTxDateFor(_cfLastTxEffectiveDate);
            string _cLasttxDateFor = LastTxDateFor(_cLastTxEffectiveDate);
            string _csLasttxDateFor = LastTxDateFor(_csLastTxEffectiveDate);
            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Last Tx Date is for";
            _drRateChange[1] =_crLasttxDateFor;
            _drRateChange[2] =_cfLasttxDateFor;
            _drRateChange[3] = _cLasttxDateFor;
            _drRateChange[4] = _csLasttxDateFor;
            _dtRateChange.Rows.Add(_drRateChange);

            //Accrued Interest Reduction
            double _crAccuredInterestReduction=AccIntReduction(_crNewFirstRepaymentDate,_crLastTxEffectiveDate,propObj.InterestRate,365);
            double _cfAccuredInterestReduction=AccIntReduction(_cfNewFirstRepaymentDate, _cfLastTxEffectiveDate, propObj.InterestRate, 365);
            double _cAccuredInterestReduction=AccIntReduction(_cNewFirstRepaymentDate, _cLastTxEffectiveDate, propObj.InterestRate, 365);
            
            //Interest from Last Tx Date
            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Interest from Last Tx Date";
            _drRateChange[1] = Interest_from_Last_Tx_Date(_crLasttxDateFor, _crNewFirstRepaymentDate, _crLastTxEffectiveDate, _crBalanceonlasttransactiondate, propObj.InterestRate, _crsof_interest, propObj.InterestRate, _crAccuredInterestReduction, "cr");
            _drRateChange[2] = Interest_from_Last_Tx_Date(_cfLasttxDateFor, _cfNewFirstRepaymentDate, _cfLastTxEffectiveDate, _cfBalanceonlasttransactiondate, propObj.InterestRate, _cfsof_interest, propObj.InterestRate, _cfAccuredInterestReduction, "cf");
            _drRateChange[3] = "";
            _drRateChange[4] = Interest_from_Last_Tx_Date(_csLasttxDateFor, _csNewFirstRepaymentDate, _csLastTxEffectiveDate, _csBalanceonlasttransactiondate, propObj.InterestRate, _cssof_interest, propObj.InterestRate, 0, "cs");
            _dtRateChange.Rows.Add(_drRateChange);

            //Previous Repayment Date
            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Previous Repayment Date";
            _drRateChange[1] =_drRateChange[2]=_drRateChange[3]=_drRateChange[4]= CDate(_cflasttransactiondate,DateFormat);
            
            // double _csAccuredInterestReduction = 0;

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Accrued Interest Reduction";
            _drRateChange[1] = _crAccuredInterestReduction; 
            _drRateChange[2] = _cfAccuredInterestReduction;
            _drRateChange[3] = _cAccuredInterestReduction;
            _drRateChange[4] = "";
            _dtRateChange.Rows.Add(_drRateChange);

            //No. of Repayments Made (M2)
             
            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "No. of Repayments Made (M2)";
            _drRateChange[1] = _crcount_of_Repayment_Made; 
            _drRateChange[2] = _cfcount_of_Repayment_Made;
            _drRateChange[3] = _ccount_of_Repayment_Made;;
            _drRateChange[4] = 0;
            _dtRateChange.Rows.Add(_drRateChange);


            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Next Repayment Date";
            _drRateChange[1] = _drRateChange[2] = _drRateChange[3] = _drRateChange[4] =CDate(_crNewFirstRepaymentDate,DateFormat);
            _dtRateChange.Rows.Add(_drRateChange);

            double _crTime_Lased_Up_To_Last_Tx_Date = Time_Lapsed_Last_Tx_Date(_crTime_Lapsed_count,propObj.Frequency);
            double _cfTime_Lased_Up_To_Last_Tx_Date = Time_Lapsed_Last_Tx_Date(_cfTime_Lapsed_count, propObj.Frequency);
            double _cTime_Lased_Up_To_Last_Tx_Date = Time_Lapsed_Last_Tx_Date(_cTime_Lapsed_count, propObj.Frequency);
            double _csTime_Lased_Up_To_Last_Tx_Date = Time_Lapsed_Last_Tx_Date(_csTime_Lapsed_count, propObj.Frequency);

            //Remaining Loan Duration

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Remaining Loan Duration";
            _drRateChange[1] = propObj.LoanDuration - _crTime_Lased_Up_To_Last_Tx_Date;
            _drRateChange[2] = propObj.LoanDuration - _cfTime_Lased_Up_To_Last_Tx_Date;
            _drRateChange[3] = propObj.LoanDuration - _cTime_Lased_Up_To_Last_Tx_Date;
            _drRateChange[4] = propObj.LoanDuration - _csTime_Lased_Up_To_Last_Tx_Date;
            _dtRateChange.Rows.Add(_drRateChange);

            //Time Lapsed up to last TX Date

            _drRateChange = _dtRateChange.NewRow();
            _drRateChange[0] = "Time Lapsed up to last TX Date";
            _drRateChange[1] = _crTime_Lased_Up_To_Last_Tx_Date;
            _drRateChange[2] = _cfTime_Lased_Up_To_Last_Tx_Date;
            _drRateChange[3] = _cTime_Lased_Up_To_Last_Tx_Date;
            _drRateChange[4] = _csTime_Lased_Up_To_Last_Tx_Date;
            _dtRateChange.Rows.Add(_drRateChange);

            //Output Table
            DataTable _dtOutputParameter = new DataTable();
            if(propObj.LoanType.ToLower()=="changerate" && IsOutput)
            {
                DataRow _drOutput;
                if (propObj.IsUpdate)
                {
                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "No. of Repayments Made";
                    _drOutput[1] = "";
                    _drOutput[2] = _crcount_of_Repayment_Made;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "No. of Repayments";
                    _drOutput[1] = "";
                    _drOutput[2] = _count;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "New Repayment";
                    _drOutput[1] = "";
                    _drOutput[2] = Repayment;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Last Repayment Date";
                    _drOutput[1] = "";
                    _drOutput[2] = CDate(_lastrepaymentdate, DateFormat);
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Last Repayment Amount";
                    _drOutput[1] = "";
                    _drOutput[2] = _lastPayment;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "";
                    _drOutput[1] = "";
                    _drOutput[2] = "Revised Schedule";
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Payment No.";
                    _drOutput[1] = "";
                    _drOutput[2] = "Due Date";
                    _drOutput[3] = "";
                    _drOutput[4] = "Amount";
                    _dtOutput.Rows.Add(_drOutput);
                    _dtOutput.Merge(_payment);
                }
                else {
                    _drOutput = _dtOutput.NewRow();
                    //Change Rate Output Table
                    _drOutput[0] = Convert.ToDouble(_crInterestLastTransDate) + Convert.ToDouble(_crLastTransDateBalance);
                    _drOutput[1] = propObj.InterestRate;
                    _drOutput[2] = Frequency(propObj.Frequency);
                    _drOutput[3] = Repayment;
                    _drOutput[4] = _count;
                    _dtOutput.Rows.Add(_drOutput);
                    _dtOutputParameter.Columns.Add("NewPaymentDue");
                    _dtOutputParameter.Columns.Add("NewInterestRate");
                    _dtOutputParameter.Columns.Add("NewTerm");

                    DataRow _drupdateoutput = _dtOutputParameter.NewRow();
                    _drupdateoutput[0] = CDate(_crNewFirstRepaymentDate, DateFormat);
                    _drupdateoutput[1] = propObj.InterestRate;
                    _drupdateoutput[2] = _crTermRemaing;
                    _dtOutputParameter.Rows.Add(_drupdateoutput);
                    _dtOutputParameter.TableName = "OutputParameter";
                    _dsDifference.Tables.Add(_dtOutputParameter);
                }
                _dtOutput.TableName = "Output";
                _dsDifference.Tables.Add(_dtOutput);
            }
           
            else if (propObj.LoanType.ToLower() == "changeallpayment" && IsOutput)
            {
                DataRow _drOutput;
                DataTable _dtNewChangeRate = new DataTable();
                if (!(propObj.IsUpdate))
                {
                    _drOutput = _dtOutput.NewRow();
                    //Change Rate Output Table
                    _drOutput[0] = Convert.ToDouble(_cInterestLastTransDate) + Convert.ToDouble(_cLastTransDateBalance);
                    _drOutput[1] = propObj.InterestRate;
                    _drOutput[2] = Frequency(propObj.Frequency);
                    _drOutput[3] = Repayment;
                    _drOutput[4] = _count;
                    _dtOutput.Rows.Add(_drOutput);

                    _dtOutputParameter.Columns.Add("startdate");
                    _dtOutputParameter.Columns.Add("newpaymentdue");

                    DataRow _drupdateoutput = _dtOutputParameter.NewRow();
                    _drupdateoutput[0] = CDate(_cNewFirstRepaymentDate, DateFormat);
                    _drupdateoutput[1] = Repayment;
                    _dtOutputParameter.Rows.Add(_drupdateoutput);
                    _dtOutputParameter.TableName = "OutputParameter";
                    _dsDifference.Tables.Add(_dtOutputParameter);
                }
                else
                {
                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Last Repayment Date";
                    _drOutput[1] = "";
                    _drOutput[2] = CDate(_lastrepaymentdate, DateFormat);
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Last Repayment Amount";
                    _drOutput[1] = "";
                    _drOutput[2] = _lastPayment;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "No. of Repayments Made";
                    _drOutput[1] = "";
                    _drOutput[2] = 0;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "No. of Repayments Remaining";
                    _drOutput[1] = "";
                    _drOutput[2] = _count;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "";
                    _drOutput[1] = "";
                    _drOutput[2] = "Revised Schedule";
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Payment No.";
                    _drOutput[1] = "";
                    _drOutput[2] = "Due Date";
                    _drOutput[3] = "";
                    _drOutput[4] = "Amount";
                    _dtOutput.Rows.Add(_drOutput);
                    _dtOutput.Merge(_payment);
                }
                _dtOutput.TableName = "Output";
                _dsDifference.Tables.Add(_dtOutput);
            }
            else if (propObj.LoanType.ToLower() == "changefrequence" && IsOutput)
            {
                DataRow _drOutput;
                if (!(propObj.IsUpdate))
                {
                    _drOutput = _dtOutput.NewRow();
                    //Change Frequency Output Table
                    _drOutput[0] = Convert.ToDouble(_cfInterestLastTransDate) + Convert.ToDouble(_cfLastTransDateBalance);
                    _drOutput[1] = propObj.InterestRate;
                    _drOutput[2] = Frequency(propObj.Frequency);
                    _drOutput[3] = Repayment;
                    _drOutput[4] = _count;
                    _dtOutput.Rows.Add(_drOutput);

                    _dtOutputParameter.Columns.Add("NewFrequency");
                    _dtOutputParameter.Columns.Add("newpaymentdue");

                    DataRow _drupdateoutput = _dtOutputParameter.NewRow();
                    _drupdateoutput[0] = propObj.Frequency;
                    _drupdateoutput[1] =CDate(_cNewFirstRepaymentDate,DateFormat);
                    _dtOutputParameter.Rows.Add(_drupdateoutput);
                    _dtOutputParameter.TableName = "OutputParameter";
                    _dsDifference.Tables.Add(_dtOutputParameter);
                }
                else
                {
                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "No. of Repayments Made";
                    _drOutput[1] = "";
                    _drOutput[2] = _cfcount_of_Repayment_Made;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "No. of Remaining Repayments";
                    _drOutput[1] = "";
                    _drOutput[2] = _count;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "New Repayment";
                    _drOutput[1] = "";
                    _drOutput[2] = Repayment;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Last Repayment Date";
                    _drOutput[1] = "";
                    _drOutput[2] = CDate(_lastrepaymentdate,DateFormat);
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Last Repayment Amount";
                    _drOutput[1] = "";
                    _drOutput[2] = _lastPayment;
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "";
                    _drOutput[1] = "";
                    _drOutput[2] = "Revised Schedule";
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _dtOutput.NewRow();
                    _drOutput[0] = "Payment No.";
                    _drOutput[1] = "";
                    _drOutput[2] = "Due Date";
                    _drOutput[3] = "";
                    _drOutput[4] = "Amount";
                    _dtOutput.Rows.Add(_drOutput);

                    _dtOutput.Merge(_payment);
                }
                _dtOutput.TableName = "Output";
                _dsDifference.Tables.Add(_dtOutput);
            }
            else if (propObj.LoanType.ToLower() == "changesinglepayment" && IsOutput)
            {
                     DataRow _drOutput;
                    _drOutput = _dtOutput.NewRow();
                    //Change Frequency Output Table
                    _drOutput[0] = Convert.ToDouble(_cInterestLastTransDate) + Convert.ToDouble(_cLastTransDateBalance);
                    _drOutput[1] = propObj.InterestRate;
                    _drOutput[2] = Frequency(propObj.Frequency);
                    _drOutput[3] = Repayment;
                    _drOutput[4] = _csnoofinstallmentreaminging;
                    _dtOutput.Rows.Add(_drOutput);

                    _drOutput = _payment.NewRow();
                    _drOutput[0] = "";
                    _drOutput[1] = "";
                    _drOutput[2] = "With Present Repayment";
                    _drOutput[3] = "";
                    _drOutput[4] = "";
                    _payment.Rows.InsertAt(_drOutput,0);
                   // _dtOutput.Merge(_payment);
                    //_dtOutputParameter.Columns.Add("NewFrequency");
                    //_dtOutputParameter.Columns.Add("newpaymentdue");

                    //DataRow _drupdateoutput = _dtOutputParameter.NewRow();
                    //_drupdateoutput[0] = propObj.Frequency;
                    //_drupdateoutput[1] = CDate(_cNewFirstRepaymentDate, DateFormat);
                    //_dtOutputParameter.Rows.Add(_drupdateoutput);
                    //_dtOutputParameter.TableName = "OutputParameter";
                    //_dsDifference.Tables.Add(_dtOutputParameter);
                _dtOutput.TableName = "Output";
                _payment.TableName="singlepayment";
                _dsDifference.Tables.Add(_payment);  
                _dsDifference.Tables.Add(_dtOutput);
            }

            //No. of Installments Remaining
            //dr[11] = _count;
            //dr[12] = _crlasttransactiondate;
            //dr[13] = _cflasttransactiondate;
            //dr[14] = dr[15] = _clasttransactiondate;

            //dr[16] = _crLastTransDateBalance;
            //dr[17] = _cfLastTransDateBalance;
            //dr[18] = _cLastTransDateBalance;
            //dr[19] = _csLastTransDateBalance;
            _dtAllValue.TableName = "DifferenceTable";
            _dtRateChange.TableName = "RateChange";
            
            _dsDifference.Tables.Add(_dtAllValue);
            _dsDifference.Tables.Add(_dtRateChange);
       
            return _dsDifference; 
        }
        public static int RepaymentRemainingFromToday(DataTable dtLoan)
        {
            //=COUNTIFS($H$18:$H$515,">="&TODAY(),$G$18:$G$515,">"&"0")
            int _RepaymentRemainingFromToday = 0;
            return _RepaymentRemainingFromToday;
        }
        /// <summary>
        /// Calculate Term Remaining
        /// </summary>
        /// <param name="FRepaymentDate">First repayment date as string that is convertabel to datetime datatype</param>
        /// <param name="LoanDuration">Loan duration as integer.</param>
        /// <param name="NewFirstRepaymentDate">New FirstRepayment Date as string that is convertabel to datetime datatype</param>
        /// <param name="LoanDate">Loan Date as string that is convertabel to datetime datatype</param>
        /// <returns></returns>
        public static double TermRemainingFromToday(string FRepaymentDate, double LoanDuration, string NewFirstRepaymentDate, string LoanDate)
        {
            //=('Front-end'!$D$8+'Front-end'!$D$5*7-MAX(Q7,$I$5))/7
            double _TermRemainingFromToday = 0;
            try { 
                DateTime _date;
                if(Convert.ToDateTime(NewFirstRepaymentDate)>Convert.ToDateTime(LoanDate))
                {
                    _date=Convert.ToDateTime(NewFirstRepaymentDate);
                }else{
                    _date=Convert.ToDateTime(LoanDate);
                }
                _TermRemainingFromToday = (Convert.ToDateTime(FRepaymentDate).AddDays(LoanDuration * 7) - _date).TotalDays / 7;
            }
            catch (Exception) { 
            }
            return _TermRemainingFromToday;
        }

        //public static string Last_Tx_date_before_Effective_Date(string installmentdate,string loandate,string NewFirstRepaymentDate)
        //{
        //    //=MAX(DMAX($H$17:$H$515,"Date",$AB$5:$AB$6),$I$5)
        //    //Last Tx date before Effective Date
        //    string _lastTxdatebeforeEffectiveDate = "";
        //    DateTime _maxDate;
        //    DateTime _maxInstallmentdate;
        //    //="<"&TEXT(MAX(Q7,$I$5),"dd-mm-yyyy")
        //    if(Convert.ToDateTime(NewFirstRepaymentDate)<Convert.ToDateTime(loandate))
        //    {
        //       _maxDate=Convert.ToDateTime(loandate);   
        //    }else{
        //       _maxDate=Convert.ToDateTime(NewFirstRepaymentDate);   
        //    }
        //    if (Convert.ToDateTime(installmentdate) < _maxDate)
        //    {
        //        _maxInstallmentdate = Convert.ToDateTime(installmentdate);
        //    }
        //    return _lastTxdatebeforeEffectiveDate;
        //}
      /// <summary>
      /// Return max date
      /// </summary>
        /// <param name="FirstDate">pass firstdate as string date should be convertable to datetime</param>
        /// <param name="Seconddate">pass firstdate as string date should be convertable to datetime</param>
      /// <returns>Exception in case date is not convertable and return firstdate</returns>
        private static string ReturnMaxDate(string FirstDate, string Seconddate)
        {
            string _returnMaxDate = "";
            try
            {
                if (Convert.ToDateTime(FirstDate) > Convert.ToDateTime(Seconddate))
                {
                    _returnMaxDate = FirstDate;
                }
                else
                {
                    _returnMaxDate = Seconddate;
                }
            }
            catch (Exception) {
                _returnMaxDate = FirstDate; 
            }
            return _returnMaxDate;
        }
      /// <summary>
      /// Calculate value for Last tx date for 
      /// </summary>
      /// <param name="LastTxdatebeforeEffectiveDate"></param>
      /// <returns></returns>
        private static string LastTxDateFor(string LastTxdatebeforeEffectiveDate)
        {
            string _lasttxdatefor = "";
            try
            {
                DateTime _temp = Convert.ToDateTime(LastTxdatebeforeEffectiveDate);
                if (EqualDate(LastTxdatebeforeEffectiveDate,getEOM(_temp.ToString())))
                {
                    _lasttxdatefor = "interest";
                }
            }
            catch (Exception) { }
            return _lasttxdatefor;
        }
      /// <summary>
        /// Calculate Accrued Interest Reduction
      /// </summary>
      /// <param name="NewFirstRepaymentDate"></param>
      /// <param name="LastTxEffectivedate"></param>
      /// <param name="IneterstRate"></param>
      /// <param name="Year"></param>
      /// <returns></returns>
        private static double AccIntReduction(string NewFirstRepaymentDate,string LastTxEffectivedate,double IneterstRate,int Year)
        {
            double _AccIntReduction = 0;
            try
            {
                //=1/(1+(Q7-EOMONTH(Q12,-1)-1)*'Front-end'!$O$4/365)
                DateTime _tempLastTxEffectiveDate = Convert.ToDateTime(LastTxEffectivedate).AddMonths(-1);
                _AccIntReduction = 1 / (1 + ((Convert.ToDateTime(NewFirstRepaymentDate) - Convert.ToDateTime(getEOM(_tempLastTxEffectiveDate.ToString()))).TotalDays - 1) * (IneterstRate/100) / Year);
            }
            catch (Exception) { }
            return  _AccIntReduction;
        }
      /// <summary>
        /// calculate Interest from Last Tx Date
      /// </summary>
      /// <returns></returns>
        private static double Interest_from_Last_Tx_Date(string LastTxDateFor,string NewFirstRepaymentDate,string LastEffectiveDate,double BalanceAsOnLastTxDate,double Interest,double s_o_f_Interest,double Max_interest_Rate,double AccuredInterestReduction,string type)
        {
            ////=(I4-'Front-end'!O4)*('Front-end'!O3-MAX(EOMONTH('Front-end'!O3,-1),Calc!I5))*Calc!Q13/365*0
            //=(I4-'Front-end'!O4)*('Front-end'!O3-MAX(EOMONTH('Front-end'!O3,-1),Calc!I5))*Calc!Q13/365*0+(IF(Q14="Interest",0,SUMIFS($M$18:$M$515,$H$18:$H$515,">"&EOMONTH(Q12,-1),$H$18:$H$515,"<="&Q12))+(Q7-Q12)/365*($I$4-'Front-end'!$O$4)*Q13)*V7
            ////(IF(Q14="Interest",0,SUMIFS($M$18:$M$515,$H$18:$H$515,">"&EOMONTH(Q12,-1),$H$18:$H$515,"<="&Q12))+(Q7-Q12)/365*($I$4-'Front-end'!$O$4)*Q13)*V7
            ////=(IF(Q14="Interest",0,SUMIFS($M$18:$M$515,$H$18:$H$515,">"&EOMONTH(Q12,-1),$H$18:$H$515,"<="&Q12))+(Q7-Q12)/365*($I$4-'Front-end'!$O$4)*Q13)*V7
            double _interest_from_Last_Tx_Date = 0;
            if (LastTxDateFor == "interest")
            {
                _interest_from_Last_Tx_Date = 0;
            }
            else {
                if (type == "cr")
                {
                    ////=(IF(Q14="Interest",0,SUMIFS($M$18:$M$515,$H$18:$H$515,">"&EOMONTH(Q12,-1),$H$18:$H$515,"<="&Q12))+(Q7-Q12)/365*($I$4-'Front-end'!$O$4)*Q13)*V7
                    _interest_from_Last_Tx_Date = (s_o_f_Interest + (Convert.ToDateTime(NewFirstRepaymentDate) - Convert.ToDateTime(LastEffectiveDate)).TotalDays / 365 * (Max_interest_Rate - Interest) * BalanceAsOnLastTxDate)*AccuredInterestReduction;
                }
                else if (type == "cf")
                {
                    //=IF(R14="Interest",0,SUMIFS($M$18:$M$515,$H$18:$H$515,">"&EOMONTH(R12,-1),$H$18:$H$515,"<="&R12))*W7
                    _interest_from_Last_Tx_Date = s_o_f_Interest * AccuredInterestReduction;
                }
                else {
                    //=IF(T14="Interest",0,SUMIFS($M$18:$M$515,$H$18:$H$515,">"&EOMONTH(T12,-1),$H$18:$H$515,"<="&T12))
                    _interest_from_Last_Tx_Date = s_o_f_Interest;
                }
            }
           // _interest_from_Last_Tx_Date = _interest_from_Last_Tx_Date * AccuredInterestReduction;
            return _interest_from_Last_Tx_Date;
        }
        
       //Time Lapsed up to last TX Date
      /// <summary>
      /// Calculate Time lapsed till lass tx date
      /// </summary>
      /// <param name="count">count is value of count less than last tx date before effective</param>
      /// <param name="freq">frequency from user input 0=weekly,1=fortnighty,2=monthly</param>
      /// <returns></returns>
        private static int Time_Lapsed_Last_Tx_Date(int count,int freq)
        {
            //=ROUNDUP(COUNTIFS($H$18:$H$515,"<="&Q12,$I$18:$I$515,">"&0)*IF($I$1=$B$1,1,IF($I$1=$B$2,2,4.3)),0)
            int _time_Lapsed_Last_Tx_Date=0;
            if (freq == 0)
            {
                _time_Lapsed_Last_Tx_Date = count;
            }
            else if (freq == 1) { _time_Lapsed_Last_Tx_Date = count*2; }
            else { _time_Lapsed_Last_Tx_Date = (int)(count*4.3);}
            return _time_Lapsed_Last_Tx_Date;
            
        }
        private static Boolean EqualDate(string FirstDate, string SecondDate)
        {
            Boolean _Val=false;
            try
            {
                if (Convert.ToDateTime(FirstDate).Date == Convert.ToDateTime(SecondDate).Date)
                {
                    _Val = true;
                }
            }
            catch (Exception) {
                _Val = false;
            }
            return _Val;
        }
        private static string GetLastTransDateBalance(string FirstDate, string SecondDate, string Balance)
        {
            string _balance = "";
            if (EqualDate(FirstDate, SecondDate))
            {
                _balance = Balance;
            }
            return _balance;
        }
        private static string InterestSinceLastTransaction(string LastTransactionDate,int yeardays,double Interest,double BalanceLastTransactionDate)
        {
            //=MAX(0,(TODAY()-Q3)/$D$4*$I$4)*Q4
            double _interestsincelasttransaction = 0;
            try
            {
              _interestsincelasttransaction=Math.Round(Math.Max(0, ((DateTime.Today - Convert.ToDateTime(LastTransactionDate)).TotalDays / yeardays * Interest)) * BalanceLastTransactionDate,2);    
            }
            catch (Exception)
            {
                _interestsincelasttransaction = 0;
            }
            return _interestsincelasttransaction.ToString();
        }
        private static string Frequency(int freq)
        {
            string _frequency = "Monthly";
            if (freq == 0)
            {
                _frequency = "Weekly";
            }
            else if (freq == 1)
            {
                _frequency = "Fortnightly";
            }
            return _frequency;
        }

        /*All date related function*/

        public static string OutputDateFormat(string input_date)
        {
            string _outputdate = "";
            CultureInfo ci = System.Threading.Thread.CurrentThread.CurrentCulture;
            string form = ci.DateTimeFormat.ShortDatePattern;
            char[] sep = ci.DateTimeFormat.DateSeparator.ToCharArray();
            string[] ind = form.Split(sep[0]);
           // int dateIndex = 0;
         //   int monthIndex = 0;
         //   int yearIndex = 0;
            string[] _tempDate = input_date.Split('/');
            _outputdate = "";
            try
            {
                    for (var i = 0; i < ind.Length; i++)
                    {
                        if (ind[i].Substring(0, 1).ToLower() == "d")
                        {
                            _outputdate += _tempDate[0];
                        }
                        if (ind[i].Substring(0, 1).ToLower() == "m")
                        {
                            _outputdate += _tempDate[1];
                        }
                        if (ind[i].Substring(0, 1).ToLower() == "y")
                        {
                            _outputdate += _tempDate[2];
                        }
                        if (i != (ind.Length - 1))
                        {
                            _outputdate += sep[0];
                        }
                    }
            }
            catch (Exception) { }
            return _outputdate;
        }
        /// <summary>
        /// Convert date into format
        /// </summary>
        /// <param name="date">give date as datetime</param>
        /// <returns></returns>
        public static string CDate(DateTime date)
        {
            CultureInfo ci = System.Threading.Thread.CurrentThread.CurrentCulture;
            string form = ci.DateTimeFormat.ShortDatePattern;
            return date.ToString(form);
        } 
        private static string getEOM(string date)
        {
              string _EOM="";
              try{
              DateTime _temp=Convert.ToDateTime(date);
              string[] _dateArray={DateTime.DaysInMonth(_temp.Year, _temp.Month).ToString(),_temp.Month.ToString(), _temp.Year.ToString()};
              CultureInfo ci = System.Threading.Thread.CurrentThread.CurrentCulture;
              string form = ci.DateTimeFormat.ShortDatePattern;
              char[] sep = ci.DateTimeFormat.DateSeparator.ToCharArray();
              string[] ind = form.Split(sep[0]);
              try
              {
                  for (var i = 0; i < ind.Length; i++)
                  {
                      if (ind[i].Substring(0, 1).ToLower() == "d")
                      {
                          _EOM += _dateArray[0];
                      }
                      if (ind[i].Substring(0, 1).ToLower() == "m")
                      {
                          _EOM += _dateArray[1];
                      }
                      if (ind[i].Substring(0, 1).ToLower() == "y")
                      {
                          _EOM += _dateArray[2];
                      }
                      if (i != (ind.Length - 1))
                      {
                          _EOM += sep[0];
                      }
                  }
              }
              catch (Exception) { }
              }catch(Exception){}
              return _EOM;
        }
        public static string CDate(string Date, string DateFormat)
        {
            string _cdate = "";
            if (DateFormat == "")
            {
                CultureInfo ci = System.Threading.Thread.CurrentThread.CurrentCulture;
                DateFormat = ci.DateTimeFormat.ShortDatePattern;
            }
            try { _cdate = Convert.ToDateTime(Date).ToString(DateFormat);}
            catch (Exception) { _cdate = Date; }
            return _cdate;
        }
        private static void insertnewdates()
        {

        }
       /****************************************************************/
 
    }
    
}